﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;


namespace PM.Services
{
    public class AdvisorServices
    {
        private static Advisor GetAdvisorFromDataReader(IDataReader dataReader)
        {
            Advisor retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Advisor();
                retVal.AdvisorId = dataReader["ADVISOR_ID"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Initials = dataReader["INITIALS"].ToString();
                retVal.Other_Names = dataReader["FULLNAME"].ToString();
                retVal.Surname = dataReader["SURNAME"].ToString();
                DateTime dob = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["DOB"].ToString(), out dob))
                    retVal.DoB = dob;
                retVal.AddressRes = dataReader["ADDRESS_RES"].ToString();
                retVal.AddressOff = dataReader["ADDRESS_OFF"].ToString();
                retVal.TeleMob = dataReader["TELE_MOB"].ToString();
                retVal.TeleOff = dataReader["TELE_OFF"].ToString();
                retVal.TeleRes = dataReader["TELE_RES"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
                int active = 0;
                int.TryParse(dataReader["ACTIVE"].ToString(), out active);
                if (active == 1)
                {
                    retVal.Active = true;
                }
                else
                {
                    retVal.Active = false;
                }
                retVal.Gender = dataReader["GENDER"].ToString();
                retVal.MaritalStatus = dataReader["MARITAL_STATUS"].ToString();
                retVal.Email = dataReader["EMAIL"].ToString();

            }
            return retVal;
        }

        public static Advisor GetAdvisorById(string advisorId)
        {
            Advisor retVal = null;
            OracleConnection con = new OracleConnection();
            
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ADVISOR WHERE ADVISOR_ID = '" + advisorId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetAdvisorFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AdvisorCollection GetAdvisorCollection()
        {
            AdvisorCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ADVISOR";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AdvisorCollection();
                    while (dataReader.Read())
                    {
                        Advisor item = GetAdvisorFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AdvisorCollection Get_Advisor_ID_Name_Collection()
        {
            AdvisorCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select t.ADVISOR_ID, t.ADVISOR_ID || ' - ' || t.TITLE || '.' || t.FULLNAME || ' ' || t.SURNAME " +
                                  "as  ADVISORNAME FROM advisor t";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AdvisorCollection();

                    while (dataReader.Read())
                    {


                        Advisor advisor = new Advisor();
                        advisor.AdvisorId = dataReader["ADVISOR_ID"].ToString();
                        advisor.Other_Names = dataReader["ADVISORNAME"].ToString();
                        if (!string.IsNullOrEmpty(advisor.AdvisorId))
                        {
                            retVal.Add(advisor);
                        }

                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetAdvisorCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ADVISOR";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

    //    public static DataTable GetAdvisorDDLDataTable()
    //    {
    //        DataTable retVal = null;
    //        OracleConnection con = new OracleConnection();

    //        try
    //        {
    //            con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    //            con.Open();
    //            OracleCommand cmd = new OracleCommand();
    //            cmd.CommandText = "select code as CODE,code as NAME from advisor";
    //            cmd.Connection = con;

    //            using (IDataReader dataReader = cmd.ExecuteReader())
    //            {
    //                if (dataReader != null)
    //                {
    //                    retVal = new DataTable();
    //                    retVal.Load(dataReader);
    //                }
    //            }
    //        }
    //        catch (System.Exception exc)
    //        {
    //            throw;
    //        }
    //        finally
    //        {
    //            con.Close();
    //        }
    //        return retVal;

    //    }

        public static void Add(Advisor advisor)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO ADVISOR"+
                                    "(ADVISOR_ID,TITLE,INITIALS,FULLNAME,SURNAME,DOB,ADDRESS_RES,ADDRESS_OFF,TELE_MOB,TELE_OFF,TELE_RES,FAX,ACTIVE,GENDER,EMAIL,MARITAL_STATUS)" +
                                    "VALUES(:v1, :v2,   :v3,    :v4,    :v5,    :v6,    :v7,    :v8,        :v9,        :v10,   :v11,   :v12, :v13, : v14, :v15, :v16)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisor.AdvisorId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = advisor.Title;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = advisor.Initials;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = advisor.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = advisor.Surname;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = advisor.DoB;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = advisor.AddressRes;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = advisor.AddressOff;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = advisor.TeleMob;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = advisor.TeleOff;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = advisor.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = advisor.Fax;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = advisor.Active;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = advisor.Gender;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = advisor.Email;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = advisor.MaritalStatus;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string advisorId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM ADVISOR WHERE ADVISOR_ID = '" + advisorId + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Advisor advisor)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE ADVISOR SET " +
                                  "FULLNAME = :v2, " +
                                  "TITLE = :v3, " +
                                  "INITIALS = :v4, " +
                                  "SURNAME = :v5, " +
                                  "DOB = :v6, " +
                                  "ADDRESS_RES = :v7, " +
                                  "ADDRESS_OFF = :v8, " +
                                  "TELE_MOB = :v9, " +
                                  "TELE_OFF = :v10, " +
                                  "TELE_RES = :v11, " +
                                  "FAX = :v12, " +
                                  "Active = :v13, " +
                                  "GENDER = :v14, " +
                                  "EMAIL = :v15, " +
                                  "MARITAL_STATUS = :v16 " + 
                                  "WHERE ADVISOR_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisor.AdvisorId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = advisor.Other_Names;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = advisor.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = advisor.Initials;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = advisor.Surname;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = advisor.DoB;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = advisor.AddressRes;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = advisor.AddressOff;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = advisor.TeleMob;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = advisor.TeleOff;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = advisor.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = advisor.Fax;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = advisor.Active;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = advisor.Gender;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = advisor.Email;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = advisor.MaritalStatus;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
